import pymysql
import pandas as pd
import time
import datetime

from com.util.MongoUtil import MongoUtil
from com.util.loggingUtil import Logs
from com.util.pushUtil import pushDingDing

log = Logs("rank_compare")

# f10-线上数据库配置
def get_f10_db_config():
    return {"host": "120.78.193.174", "port": 5678, "database": "ranking", "user": "root", "password": "Shizhifengyun20160426."}


def _filter_code(code: str) -> bool:
    """
    过滤出有效代码
    :param code:
    :return:
    """
    return code.startswith("000") or \
        code.startswith("001") or \
        code.startswith("002") or \
        code.startswith("003") or \
        code.startswith("300") or \
        code.startswith("301") or \
        code.startswith("159") or \
        code.startswith("16") or \
        code.startswith("1848") or \
        code.startswith("501") or \
        code.startswith("502") or \
        code.startswith("506") or \
        code.startswith("508") or \
        code.startswith("180") or \
        code.startswith("60") or \
        code.startswith("68") or \
        code.startswith("56") or \
        code.startswith("51") or \
        code.startswith("58")

# 判断是否是当天日期
def _is_current_day(v, today):
    return v[2].to_pydatetime() == today

# 查询大数据库每个code最新日期的排名的sql
sql = """
SELECT
  a.ranking, a.code, a.date
FROM
  FloatingScore a,
  (
  SELECT
    b.code AS code,
    max( b.date ) AS date 
  FROM
    FloatingScore b 
  GROUP BY
    b.code 
  ) d 
WHERE 
   d.code = a.code 
  AND a.date = d.date 
ORDER BY
  a.date ASC
"""

def rank_compare():
    # 错误列表
    error_list = []
    # 获取今日零点的datetime
    now_time = int(time.time())
    day_time = now_time - now_time % 86400 + time.timezone
    today = datetime.datetime.fromtimestamp(day_time)
    # pandas读取mysql，获取数据
    df = pd.read_sql_query(sql, pymysql.connect(**get_f10_db_config()))
    df['code'] = df.apply(lambda x: x['code'].rsplit('.')[0], axis=1)
    with MongoUtil('stock_base_info') as mu, MongoUtil("stock_type_property") as mu1:
        mu_res = mu.find_many({
            "flag": True,
            "market_type": {
                "$in": [1, 2, 18]
            },
            "stock_type": {
                "$in": [8448, 8464, 8480, 8496]
            }
        })
        for code in mu_res:
            # 这些代码是年报未披露，无法计算评分数据的，所以自选列表和f10排名会不一致，过滤掉
            if code['stock_id'] in ['300312', '000420', '600532', '300108',
                                    '002411', '600090', '600146', '002776',
                                    '600781', '002433', '002118'] \
            or _filter_code(code['stock_id']) is False:
                continue
            res = mu1.find_one({
                "stock_id": code['stock_id'],
                "flag": True,
            })
            if res is None:
                continue
            # 获取排名
            rank = int(res['wogoorank'])
            pd_res = df.query(f'code == "{code["stock_id"]}"')
            if len(pd_res) > 0:
                pd_rank = pd_res.values[0][0] if _is_current_day(pd_res.values[0], today) else 0
            else:
                pd_rank = 0
            try:
                assert rank == pd_rank
                log.info(f"{code['stock_id']}在mongo中排名为:{rank}, 详情页中是{pd_rank}")
            except Exception as e:
                error_list.append(code)
                log.error(f"列表和详情页排名不一致，{code}在mongo中排名为:{rank}, 详情页中是{pd_rank}")
        if error_list:
            log.error(f"吾股排名详情页和列表对比，对比总数为{len(mu_res)},自选列表对比详情页排名失败的为{len(error_list)}个,为{error_list}")
            return f"吾股排名详情页和列表对比，对比总数为{len(mu_res)}个,失败的有{len(error_list)}个,为{error_list}"
        else:
            log.info(f"吾股排名详情页和列表对比，对比总数为{len(mu_res)},对比完成，没有错误。")
            return f"吾股排名详情页和列表对比，对比总数为{len(mu_res)}个,对比完成，没有发现错误。"

def run():
    pushDingDing(rank_compare())

if __name__ == '__main__':
    run()


